
Create PROCEDURE [dbo].[CalcIncrementalDeductionFactor] @FactorId BIGINT, @KindFactor TINYINT, @PersonCode VARCHAR(5) , @PersonPercent REAL , @IsCalcIncrementalDeductionRow BIT
AS
	BEGIN TRY
	BEGIN TRAN

    DECLARE @TotPrice MONEY
	DECLARE @TotTax MONEY 

	DECLARE @IncrementalDeduction AS TABLE
    (
	   Code SMALLINT,
	   Name NVARCHAR(30),
	   StartDate DATETIME,
	   EndDate DATETIME,
	   MaxPrice MONEY,
	   MaxPercent FLOAT,
	   ActCode SMALLINT,
	   DeviceShow SMALLINT,
	   Sorting CHAR(1),
	   IsCalculate BIT ,
	   IsActive BIT
	)

	INSERT INTO @IncrementalDeduction(Code,Name,StartDate,EndDate,MaxPrice,MaxPercent, DeviceShow, IsActive, Sorting,IsCalculate, ActCode)
	SELECT AutoId,Name,AzTarikh,TaTarikh,Max_Price,Max_Percent,ShowPrice,Active, SortDisplay,ISNULL(Formula,''), Act_Code FROM dbo.Over_Under WITH (NOLOCK)
             WHERE Action_Code = @KindFactor AND Active = 1 AND CONVERT(VARCHAR(10), GETDATE(),111) BETWEEN AzTarikh AND TaTarikh

	SELECT @TotPrice = 
	SUM(CASE WHEN @KindFactor IN (2,3,4,8)     THEN K_Qty1 * Price_KHarid 
	         WHEN @KindFactor IN (5,6,7,8,9,0) THEN K_Qty1 * Price_Forosh END),

	@TotTax = SUM(CASE WHEN @KindFactor IN (2,3,4,8) AND ISNULL(Takhfif_Flag,'') = 1 THEN K_Qty1 * Price_KHarid 
	         WHEN @KindFactor IN (5,6,7,8,9,0) AND ISNULL(Takhfif_Flag,'') = 1 THEN K_Qty1 * Price_Forosh END)
    FROM dbo.TmpHavaleh WITH (NOLOCK)
	WHERE Id_Havaleh = @FactorId
        
	---------(Delete Item IncrementalDeductionFactor When Is Not Active Or CurrentDate Not Range Date Define)-----------
	DELETE dbo.Tmp_Over_Under_Row FROM dbo.Tmp_Over_Under_Row
	INNER JOIN @IncrementalDeduction ON [@IncrementalDeduction].Code = Tmp_Over_Under_Row.Code_Over_Under
	INNER JOIN dbo.TmpHavaleh WITH (NOLOCK) ON TmpHavaleh.AutoId = Tmp_Over_Under_Row.AutoId
	WHERE Id_Havaleh = @FactorId
	AND (CONVERT(VARCHAR(10), GETDATE(),111) < StartDate OR CONVERT(VARCHAR(10), GETDATE(),111) > EndDate OR IsActive =  0 OR [@IncrementalDeduction].DeviceShow NOT IN (0,4))

	DELETE dbo.Tmp_Over_Under_Factor FROM dbo.Tmp_Over_Under_Factor
	INNER JOIN Tmpheadh ON TmpHeadH.Id_Havaleh = Tmp_Over_Under_Factor.Id_Havaleh
	INNER JOIN @IncrementalDeduction ON [@IncrementalDeduction].Code = Tmp_Over_Under_Factor.Code_Over_Under
	WHERE TmpHeadh.Id_Havaleh = @FactorId
	AND (CONVERT(VARCHAR(10), GETDATE(),111) < StartDate OR CONVERT(VARCHAR(10), GETDATE(),111) > EndDate OR IsActive =  0 OR [@IncrementalDeduction].DeviceShow <> 0)
	AND Code_Over_Under NOT IN 
	   (SELECT autoid FROM dbo.Over_Under WHERE ShowPrice=4)
	   AND (ISNULL(Items,0) <> -1)
	--------------------------------------------------------------------------------------------------------------------

	--IF @IsCalcIncrementalDeductionRow = 1 
	--BEGIN 
	EXEC dbo.Sp_Auto_OURow_Factor @KindFactor, @FactorId, @PersonPercent

	INSERT INTO dbo.Tmp_Over_Under_Factor(Id_Havaleh,Name,Code_Over_Under, Type,Price, [Percent], Act_Code, Auto_id_Havaleh, Price_Row, Guy_Code, Max_Price, Max_percent, ShowPrice)
	SELECT Id_Havaleh, [@IncrementalDeduction].name,  Code_Over_Under,dbo.Tmp_Over_Under_Row.Type, ISNULL(Sum(Price),0) Price, 0, ActCode, 0, 0, @PersonCode, 0, 0 ,DeviceShow
	FROM Tmp_Over_Under_Row With (Nolock) Inner Join TmpHavaleh With (Nolock)
	ON Tmp_Over_Under_Row.AutoId = TmpHavaleh.AutoId
	INNER JOIN @IncrementalDeduction ON [@IncrementalDeduction].Code = Tmp_Over_Under_Row.Code_Over_Under
	WHERE TmpHavaleh.Id_Havaleh = @FactorId 
	AND NOT EXISTS(SELECT * FROM dbo.Tmp_Over_Under_Factor WHERE Id_Havaleh = @FactorId AND Code_Over_Under = dbo.Tmp_Over_Under_Row.Code_Over_Under)
	GROUP By Code_Over_Under, Id_Havaleh, [@IncrementalDeduction].Name, Tmp_Over_Under_Row.Type, ActCode, [@IncrementalDeduction].DeviceShow,Price_Row, Sorting
	ORDER BY [@IncrementalDeduction].Sorting
	--END

	UPDATE dbo.Tmp_Over_Under_Factor SET price = PriceRow FROM dbo.Tmp_Over_Under_Factor
	INNER JOIN 
	(
		SELECT Code_Over_Under , SUM(Price) PriceRow FROM dbo.Tmp_Over_Under_Row WITH (NOLOCK)
		INNER JOIN dbo.TmpHavaleh WITH (NOLOCK) ON TmpHavaleh.AutoId = Tmp_Over_Under_Row.AutoId
		WHERE Id_Havaleh = @FactorId
		GROUP BY Code_Over_Under
	) AS T ON T.Code_Over_Under = dbo.Tmp_Over_Under_Factor.Code_Over_Under
	WHERE Id_Havaleh = @FactorId
	AND Tmp_Over_Under_Factor.Price <> T.PriceRow AND (dbo.Tmp_Over_Under_Factor.ShowPrice <> 4);

	UPDATE dbo.Tmp_Over_Under_Factor SET [Percent] = 
	                                                CASE
                                                      WHEN IsCalculate = 0 THEN (
													                                CASE 
																						WHEN MaxPercent = 0 THEN (Price*100)/@TotPrice
																						ELSE
                                                                                        (
																						    CASE 
																							WHEN ROUND(CalcPercent,0) <> ROUND(MaxPercent,0) THEN CAST([Percent] AS DEC(16, 6)) * 100 / @TotPrice
																							ELSE MaxPercent
																							END 
																						)
																					END 
																				)
                                                      WHEN IsCalculate = 1 THEN (
													                                CASE 
																						WHEN MaxPercent = 0 THEN (Price*100)/@TotTax
																						ELSE
                                                                                        (
																						    CASE 
																							WHEN ROUND(CalcPercent,0) <> ROUND(MaxPercent,0) THEN CAST([Percent] AS DEC(16, 6)) * 100 / @TotTax
																							ELSE MaxPercent
																							END 
																						)
																					END 
																				)
                                                    END 
	FROM dbo.Tmp_Over_Under_Factor
	INNER JOIN @IncrementalDeduction ON [@IncrementalDeduction].Code = Code_Over_Under
	INNER JOIN
	(
		SELECT Code_Over_Under AutoId, SUM([Percent])/COUNT([Percent]) CalcPercent FROM dbo.Tmp_Over_Under_Row
		INNER JOIN dbo.TmpHavaleh WITH (NOLOCK) ON TmpHavaleh.AutoId = Tmp_Over_Under_Row.AutoId
		WHERE Id_Havaleh = @FactorId AND price <> 0 
		GROUP BY Code_Over_Under 
	) AS OverUnderRow ON OverUnderRow.AutoId = Code   
	WHERE Id_Havaleh = @FactorId AND dbo.Tmp_Over_Under_Factor.ShowPrice <> 4
    													                                

	UPDATE dbo.Tmp_Over_Under_Factor SET Guy_Code = @PersonCode WHERE Id_Havaleh = @FactorId AND Guy_Code <> @PersonCode
	DELETE FROM dbo.Tmp_Over_Under_Factor WHERE price = 0 AND [Percent] = 0 AND Auto_id_Havaleh = 0 AND Id_Havaleh = @FactorId AND ShowPrice = 1
	DELETE FROM dbo.Tmp_Over_Under_Factor WHERE ShowPrice = 2 AND @PersonPercent = 0 AND Id_Havaleh = @FactorId

	COMMIT TRAN
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;
		SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH


